﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using XLETL.TransformationPluginManager;
using XLETL.Model;

namespace XLETL.TransformationPlugins
{
    [Plugin("Lookup", "Matches text in an input column with a reference table", "Source")]
    public class Lookup : UserControl, IPluginControl
    {
        #region Form Controls

        private GroupBox groupBox1;
        private Label label2;
        private ComboBox cbDisplayColumn;
        private Label label1;
        private Label label3;
        private ComboBox cbValueColumn;
        private ComboBox cbLookupTable;

        #endregion

        /// <summary>
        /// Database connection string parameter
        /// </summary>
        private string DBConnStringParam { get; set; }
        private string LookupTableName { get; set; }
        private string DisplayMember { get; set; }
        private string ValueMember { get; set; }
        private DataTable TransformationData { get; set; }        

        public Lookup(Parameter[] pluginParams, Parameter[] transformationParams)
        {
            InitializeComponent();

            for (int i = 0; i < pluginParams.Length; i++)
            {
                if (pluginParams[i].ParameterType.Equals(PluginInfo.PARAM_GLOBAL_DB_CONN_STRING))
                {
                    DBConnStringParam = Convert.ToString(pluginParams[i].ParameterValue);
                }
                else if (pluginParams[i].ParameterType.Equals(PluginInfo.PARAM_GLOBAL_FOREIGN_TABLE_NAME))
                {
                    LookupTableName = Convert.ToString(pluginParams[i].ParameterValue);
                }
            }

            PopulateTableList(LookupTableName);

            // if the control in edit mode then assign the appropriate values
            if (transformationParams != null && transformationParams.Length > 0)
            {
                LookupTableName = transformationParams[0].ParameterValue.ToString();
                ValueMember = transformationParams[1].ParameterValue.ToString();
                DisplayMember = transformationParams[2].ParameterValue.ToString();
                
                // now assign recieved values to the form controls
                cbLookupTable.Text = LookupTableName;
                cbDisplayColumn.Text = DisplayMember;
                cbValueColumn.Text = ValueMember;
            }            
        }

        private void InitializeComponent()
        {
            this.groupBox1 = new System.Windows.Forms.GroupBox();
            this.label3 = new System.Windows.Forms.Label();
            this.cbValueColumn = new System.Windows.Forms.ComboBox();
            this.label2 = new System.Windows.Forms.Label();
            this.cbDisplayColumn = new System.Windows.Forms.ComboBox();
            this.label1 = new System.Windows.Forms.Label();
            this.cbLookupTable = new System.Windows.Forms.ComboBox();
            this.groupBox1.SuspendLayout();
            this.SuspendLayout();
            // 
            // groupBox1
            // 
            this.groupBox1.Controls.Add(this.label3);
            this.groupBox1.Controls.Add(this.cbValueColumn);
            this.groupBox1.Controls.Add(this.label2);
            this.groupBox1.Controls.Add(this.cbDisplayColumn);
            this.groupBox1.Controls.Add(this.label1);
            this.groupBox1.Controls.Add(this.cbLookupTable);
            this.groupBox1.Dock = System.Windows.Forms.DockStyle.Fill;
            this.groupBox1.Location = new System.Drawing.Point(0, 0);
            this.groupBox1.Name = "groupBox1";
            this.groupBox1.Size = new System.Drawing.Size(325, 125);
            this.groupBox1.TabIndex = 0;
            this.groupBox1.TabStop = false;
            this.groupBox1.Text = "Lookup";
            // 
            // label3
            // 
            this.label3.AutoSize = true;
            this.label3.Location = new System.Drawing.Point(16, 79);
            this.label3.Name = "label3";
            this.label3.Size = new System.Drawing.Size(98, 13);
            this.label3.TabIndex = 5;
            this.label3.Text = "Reference Column:";
            // 
            // cbValueColumn
            // 
            this.cbValueColumn.FormattingEnabled = true;
            this.cbValueColumn.Location = new System.Drawing.Point(136, 77);
            this.cbValueColumn.Name = "cbValueColumn";
            this.cbValueColumn.Size = new System.Drawing.Size(165, 21);
            this.cbValueColumn.TabIndex = 4;
            // 
            // label2
            // 
            this.label2.AutoSize = true;
            this.label2.Location = new System.Drawing.Point(16, 52);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(82, 13);
            this.label2.TabIndex = 3;
            this.label2.Text = "Display Column:";
            // 
            // cbDisplayColumn
            // 
            this.cbDisplayColumn.FormattingEnabled = true;
            this.cbDisplayColumn.Location = new System.Drawing.Point(136, 49);
            this.cbDisplayColumn.Name = "cbDisplayColumn";
            this.cbDisplayColumn.Size = new System.Drawing.Size(165, 21);
            this.cbDisplayColumn.TabIndex = 2;
            // 
            // label1
            // 
            this.label1.AutoSize = true;
            this.label1.Location = new System.Drawing.Point(16, 21);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(76, 13);
            this.label1.TabIndex = 1;
            this.label1.Text = "Lookup Table:";
            // 
            // cbLookupTable
            // 
            this.cbLookupTable.FormattingEnabled = true;
            this.cbLookupTable.Location = new System.Drawing.Point(136, 19);
            this.cbLookupTable.Name = "cbLookupTable";
            this.cbLookupTable.Size = new System.Drawing.Size(165, 21);
            this.cbLookupTable.TabIndex = 0;
            this.cbLookupTable.SelectedIndexChanged += new System.EventHandler(this.cbLookupTable_SelectedIndexChanged);
            // 
            // Lookup
            // 
            this.Controls.Add(this.groupBox1);
            this.Name = "Lookup";
            this.Size = new System.Drawing.Size(325, 125);
            this.groupBox1.ResumeLayout(false);
            this.groupBox1.PerformLayout();
            this.ResumeLayout(false);

        }

        #region Designer Methods

        private void PopulateTableList(string tableName)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                try
                {
                    DataTable dtTblList = GetDatabaseTableList(DBConnStringParam);
                    cbLookupTable.DisplayMember = "TABLE_NAME";
                    cbLookupTable.ValueMember = "TABLE_NAME";
                    cbLookupTable.DataSource = dtTblList;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else
            {
                cbLookupTable.Items.Add(tableName);
                cbLookupTable.Text = tableName;
            }
        }

        public DataTable GetColumnListByTable(string tableName, string connString)
        {
            DataTable dtResult = new DataTable();

            try
            {
                using (SqlConnection con = new SqlConnection(connString))
                {
                    con.Open();

                    // Create and configure a new command.
                    using (SqlCommand com = con.CreateCommand())
                    {
                        com.CommandType = CommandType.Text;
                        com.CommandText = string.Format("select c.column_id, c.name as ColumnName, t.name as TypeName, c.max_length as TypeLength, c.precision as TypePrecision, c.scale as TypeScale from sys.objects as o inner join sys.columns as c on o.object_id = c.object_id inner join sys.types as t on t.system_type_id = c.system_type_id and t.user_type_id = c.user_type_id left outer join sys.index_columns as ic on c.column_id = ic.column_id and o.object_id = ic.object_id where o.name = '{0}'", tableName);

                        dtResult.Load(com.ExecuteReader(CommandBehavior.CloseConnection));
                    }
                }
            }
            catch (Exception ex)
            {
                // Todo: the message should be displayed in the plugin arrea
                throw ex;
            }

            return dtResult;

        }

        /// <summary>
        /// Get table list for particular database specified in the connection string
        /// </summary>
        /// <param name="connectionString">Connection string to the target database</param>
        public DataTable GetDatabaseTableList(string connectionString)
        {
            DataTable dtResult = new DataTable();
            try
            {
                dtResult = GetDBSchemaTable("System.Data.SqlClient", connectionString, "Tables", null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dtResult;
        }


        private DataTable GetDBSchemaTable(string providerName, string cnSettings, string collectionName, string[] restrictions)
        {
            DbProviderFactory provider = DbProviderFactories.GetFactory(providerName);

            using (DbConnection cn = provider.CreateConnection())
            {
                cn.ConnectionString = cnSettings;
                cn.Open();
                if (collectionName == null)
                {
                    return cn.GetSchema();
                }
                return cn.GetSchema(collectionName, restrictions);
            }
        }

        private void cbLookupTable_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbLookupTable.SelectedItem != null)
            {
                DataTable columnList = GetColumnListByTable(cbLookupTable.Text, DBConnStringParam);
                cbDisplayColumn.DataSource = null;
                cbDisplayColumn.DisplayMember = "ColumnName";
                cbDisplayColumn.ValueMember = "ColumnName";
                cbDisplayColumn.DataSource = columnList;

                cbValueColumn.DataSource = null;
                cbValueColumn.DisplayMember = "ColumnName";
                cbValueColumn.ValueMember = "ColumnName";
                cbValueColumn.DataSource = columnList.Copy();                
            }
        }

        public Parameter[] GetTransformationParamList()
        {
            Parameter lookupTableParam = new Parameter()
            {
                ParameterName = "LookupTableName",
                ParameterType = "System.String",
                ParameterValue = cbLookupTable.Text
            };
            Parameter displayMemberParam = new Parameter()
            {
                ParameterName = "DisplayMember",
                ParameterType = "System.String",
                ParameterValue = cbDisplayColumn.Text
            };
            Parameter valueMemberParam = new Parameter()
            {
                ParameterName = "ValueMember",
                ParameterType = "System.Int32",
                ParameterValue = cbValueColumn.Text
            };

            return new Parameter[] { lookupTableParam, valueMemberParam, displayMemberParam };        
        }

        #endregion

        #region Transformation Methods

        private Hashtable LoadDataFromTable()
        {
            Hashtable lookupTableData = new Hashtable();

            try
            {
                using (SqlConnection con = new SqlConnection(DBConnStringParam))
                {
                    con.Open();

                    // Create and configure a new command.
                    using (SqlCommand com = con.CreateCommand())
                    {
                        com.CommandType = CommandType.Text;
                        com.CommandText = string.Format("Select [{0}], [{1}] from [{2}]", ValueMember, DisplayMember, LookupTableName);

                        using (SqlDataReader reader = com.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                              try
                              {
                                // TODO: make sure the key value is unique as it is possible to have same name with different id records
                                lookupTableData.Add(reader.GetValue(1), reader.GetValue(0));
                              }
                              catch (Exception)
                              {
                                // ignore exception if cannot add key-value pair based on the duplicate key
                              }
                            }
                        }                        
                    }
                }
            }
            catch (Exception ex)
            {
                // Todo: the message should be displayed in the plugin arrea
                throw ex;
            }

            return lookupTableData;
        }

        public bool DoTransformation(DataTable data, string targetColumnName)
        {
            bool isSuccess = true;

            object objToRepl = null;

            Hashtable lookupTableData = LoadDataFromTable();

            if (lookupTableData.Count > 0)
            {

                foreach (DataRow dr in data.Rows)
                {
                    objToRepl = dr[targetColumnName];

                    if (objToRepl != null && objToRepl != DBNull.Value)
                    {
                        object correctValue = lookupTableData[objToRepl];
                        dr[targetColumnName] = correctValue;
                    }
                }

            }
            else
            {
                isSuccess = false;
            }

            return isSuccess;
        }


        #endregion

    }
}
